﻿Imports System.Data.SqlClient
'Imports Microsoft.Office.Interop
'Imports Microsoft.Office.Core
Module ModuleCungCapThongTin
    Public m_tennguoiyeucau As String
    Public m_donvi As String
    Public m_mucdich As String
    Public m_hinhthuc As String
    Public m_ngaymuon As Date
    ' 
    Public m_chusudungdat As String
    Public m_diachi As String
    Public m_sohoso As String

    ' frmCC new FrmCCTTHoSoCapGiay()
End Module

Public Class FrmCCTTHoSoCapGiay

    Private Sub NapHinhThuc()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHinhThuc", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHinhThuc.DisplayMember = "TenHinhThuc"
        CmbHinhThuc.ValueMember = "maHinhThucID"
        CmbHinhThuc.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbhuyen.DisplayMember = "T_huyen"
        cmbhuyen.ValueMember = "maH"
        cmbhuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBox()
        TextSoHoSo.DataBindings.Clear()
        TextSoHoSo.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "SoHoSo")
        TextTenChu.DataBindings.Clear()
        TextTenChu.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenchu")
        TextDiaChi.DataBindings.Clear()
        TextDiaChi.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "diachi")
        CmbTinhTrang.DataBindings.Clear()
        CmbTinhTrang.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenTinhTrang")
        TextPhong.DataBindings.Clear()
        TextPhong.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "Tenphong")
        TextNgan.DataBindings.Clear()
        TextNgan.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenNgan")
        TextKe.DataBindings.Clear()
        TextKe.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenKe")
        TextKho.DataBindings.Clear()
        TextKho.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "TenKho")
        TextMaVach.DataBindings.Clear()
        TextMaVach.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "MaVach")
        NapDataGridViewThuaDat()
        CmbXa.DataBindings.Clear()
        CmbXa.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "tenxa")
        cmbhuyen.DataBindings.Clear()
        cmbhuyen.DataBindings.Add("Text", DataGridViewTenChu.DataSource, "T_huyen")

    End Sub
    Private Sub Lamtuoi()
        TextSoHoSo.Text = ""
        TextTenChu.Text = ""
        TextDiaChi.Text = ""
        TextSoThua.Text = ""
        TextToBD.Text = ""
        TextDienTich.Text = ""
        TextLoaiDat.Text = ""
        TextMaVach.Text = ""
        TextLoaiTaiLieu.Text = ""
        TextPhong.Text = ""
        TextNgan.Text = ""
        TextKe.Text = ""
        TextKho.Text = ""
        TextthuaID.Text = ""
        TextNguoiYeuCau.Text = ""
        TextDonVi.Text = ""
        TextMucDich.Text = ""
        DataGridViewNguoiMuon.Rows.Clear()
    End Sub
    Private Sub NapVaoTexBoxThuaDat()
        TextthuaID.DataBindings.Clear()
        TextthuaID.DataBindings.Add("text", DataGridViewThuaDat.DataSource, "thuaid")
        TextSoThua.DataBindings.Clear()
        TextSoThua.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "thuadat")
        TextToBD.DataBindings.Clear()
        TextToBD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "tobando")
        TextDienTich.DataBindings.Clear()
        TextDienTich.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Dientich")
        TextLoaiDat.DataBindings.Clear()
        TextLoaiDat.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Loaidat")
        TextLoaiTaiLieu.DataBindings.Clear()
        TextLoaiTaiLieu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenLoaitailieu")
    End Sub
    Private Sub Update_Tinhtrang()
        Try
            Dim Sql As String = ""
            Sql = " Update TENCHULANDAU Set " &
                " TinhTrangid = '" & 2 & "'" &
                " where SoHoSo = '" & TextSoHoSo.Text & "' "
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            '   MsgBox("Sửa tên chủ thành công!")
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

    End Sub

    Private Sub NapDataGridView()

        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select  ten.SohoSo, ten.TenChu, ten.DiaChi, ten.mavach," &
                                " tt.tentinhtrang, p.tenphong, ng.tenngan, k.tenke, kh.tenkho,  tx.tenxa, th.T_huyen  " &
                                " From TENCHULANDAU ten, DMTinhTrang tt,  " &
                                " DMPhong p, DMNgan ng, DMKe k, DMKho kh, tenxa tx, tenhuyen th  " &
                                " where TinhtrangID= tt.matinhtrangid " &
                                " and ten.phongID = p.maphongid " &
                                " and ten.nganid = ng.manganid " &
                                " and ten.keid = k.makeid " &
                                " and ten.khoid = kh.makhoid " &
                                " and ten.xaid = tx.maxa" &
                                " and tinhtrangid =  '" & 1 & "' " &
                                " and ten.huyenid = '" & cmbhuyen.SelectedValue.ToString & "' " &
                                " and ten.xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                                " and ten.huyenID = th.mah ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        '
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("không có thông tin")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewten()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select ten.SohoSo, ten.TenChu, ten.DiaChi, ten.mavach," &
                                " tt.tentinhtrang, p.tenphong, ng.tenngan, k.tenke, kh.tenkho,  tx.tenxa, th.T_huyen  " &
                                " From TENCHULANDAU ten, DMTinhTrang tt,  " &
                                " DMPhong p, DMNgan ng, DMKe k, DMKho kh, tenxa tx, tenhuyen th  " &
                                " where TinhtrangID= tt.matinhtrangid " &
                                " and ten.phongID = p.maphongid " &
                                " and ten.nganid = ng.manganid " &
                                " and ten.keid = k.makeid " &
                                " and ten.khoid = kh.makhoid " &
                                " and ten.xaid = tx.maxa" &
                                " and  ten.TenChu like N'%" & TextTenChu.Text & "%' " &
                                " and tinhtrangid =  '" & 1 & "' " &
                                " and ten.huyenID = th.mah ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTenChu.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("không có thông tin")
            Return
        End If
        ob_cnn.Close()
    End Sub
    Private Sub LuuDanhSachNguoiMuon()
        Try
            Dim NgayMuon As String
            NgayMuon = String.Format("{0:MM/dd/yyyy}", DateNgayMuon.Value)
            Dim Sql As String = ""
            Sql = " insert into CCTT_HOSOCAPGIAY(SoHoSo, ngayMuon, NguoiMuon, DonVi,HinhThucMuonId,MucDichMuon, tinhtrangid,xaid, huyenid ) " &
                " values ('" & TextSoHoSo.Text & "'," &
                " '" & NgayMuon & "'," &
                " N'" & TextNguoiYeuCau.Text & "'," &
                " N'" & TextDonVi.Text & "'," &
                " '" & CmbHinhThuc.SelectedValue.ToString & "'," &
                " N'" & TextMucDich.Text & "', " &
                " '" & 2 & "', " &
                " '" & CmbXa.SelectedValue.ToString & "'," &
                " '" & cmbhuyen.SelectedValue.ToString & "')"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub LuuNguoiCungCap()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim Sql As String = ""
            Sql = " insert into QL_CCTTCAPGIAY(SoHoSo, Nguoicungcap, ngaycungcap ) " &
                " values ('" & TextSoHoSo.Text & "'," &
                "  N'" & tennhap & "'," & Format(Now.ToOADate() - 2) & " )"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub NapDataGridViewThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.thuaid, thua.sohoso, thua.thuadat, thua.tobando, thua.dientich, thua.loaidat, " &
                                " tl.tenloaitailieu " &
                                " From  THUADATLANDAU thua,  DMTailieu tl  " &
                                " WHERE thua.sohoso = '" & TextSoHoSo.Text & "' " &
                                 " and thua.loaitailieu = tl.maloaitailieuID ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub InPhieu()

    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbhuyen.SelectedIndexChanged
        ' load ten xa
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & cmbhuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub FrmSuaHoSoCapGiay_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapHinhThuc()
        NapTinhTrang()
        NapDonViHuyen()
        DateNgayMuon.Text = Today()
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewTenChu.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTenChu.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewNguoiMuon.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewNguoiMuon.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure

    End Sub

    Private Sub TextTenChu_TextChanged(sender As Object, e As EventArgs) Handles TextTenChu.TextChanged
        NapDataGridViewten()
    End Sub
    Private Sub DataGridViewTenChu_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTenChu.CellClick
        NapVaoTexBox()
    End Sub
    Private Sub DataGridViewThuaDat_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellClick
        NapVaoTexBoxThuaDat()
    End Sub
    Private Sub ButtonTimKiem_Click(sender As Object, e As EventArgs) Handles ButtonTimKiem.Click
        Lamtuoi()
        NapDataGridView()
        ButtonLuu.Enabled = True
    End Sub
    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()

    End Sub
    Private Sub NapNguoiMuon()
        DataGridViewNguoiMuon.Rows.Add(TextNguoiYeuCau.Text, TextDonVi.Text)
    End Sub

    Private Sub ButtonLuu_Click(sender As Object, e As EventArgs) Handles ButtonLuu.Click
        Dim NgayNhap As String
        NgayNhap = String.Format("{0:MM/dd/yyyy}", DateNgayMuon.Value)

        If TextNguoiYeuCau.Text <> "" And TextDonVi.Text <> "" And TextDiaChi.Text <> "" And TextTenChu.Text <> "" Then
            LuuDanhSachNguoiMuon()
            LuuNguoiCungCap()
            NapNguoiMuon()
            Update_Tinhtrang()
            '    ButtonLuu.Enabled = False
            'luu vao bien toan cuc
            m_tennguoiyeucau = TextNguoiYeuCau.Text
            m_donvi = TextDonVi.Text
            m_mucdich = TextMucDich.Text
            m_hinhthuc = CmbHinhThuc.Text
            m_ngaymuon = NgayNhap
            ' 
            'Public m_chusudungdat As String
            'Public m_diachi As String
            'Public m_sohoso As String
            MessageBox.Show("Lưu thông tin thành công ")
            NapDataGridView()
        Else
            MessageBox.Show(" Chưa có thông tin người yêu cầu ! ")
        End If

    End Sub
    Private Sub ButtonInPhieu_Click(sender As Object, e As EventArgs) Handles ButtonInPhieu.Click
        If TextSoHoSo.Text = Nothing Then
            MessageBox.Show("Chọn hồ sơ cần thao tác!")
            Return
        End If
        Dim ts() As String = {"<SoPhieuMuon>", "<NguoiMuon>", "<DonVi>", "<SoHoSo>", "<TenChuSuDung>", "<DiaChi>", "<ToBanDo>", "<ThuocHuyen>", "<ThuocXa>", "<TenKho>", "<TenKe>", "<TenPhong>", "<MucDichMuon>", "<HinhThuc>", "<NgayMuon>", "<NguoiNhap>"}
        Dim gt() As String = {AutoID("CCTT_HOSOCAPGIAY"), TextNguoiYeuCau.Text, TextDonVi.Text, TextSoHoSo.Text, TextTenChu.Text, TextDiaChi.Text, TextToBD.Text, cmbhuyen.Text.Trim, CmbXa.Text.Trim, TextKho.Text.Trim, TextKe.Text.Trim, TextPhong.Text.Trim, TextMucDich.Text, CmbHinhThuc.Text.ToString, DateNgayMuon.Text, GetTenNguoiNhap(ModuleTenDangNhap.m_tennhap)}
        ExportWord(ts, gt, "PhieuMuonHoSoCapGiay", AutoID("CCTT_HOSOCAPGIAY"))
    End Sub
    Private Sub DataGridViewNguoiMuon_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewNguoiMuon.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewNguoiMuon.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewNguoiMuon.RowHeadersWidth < Size.Width + 20 Then DataGridViewNguoiMuon.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DataGridViewThuaDat_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewThuaDat.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewThuaDat.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewThuaDat.RowHeadersWidth < Size.Width + 20 Then DataGridViewThuaDat.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DataGridViewTenChu_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewTenChu.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTenChu.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTenChu.RowHeadersWidth < Size.Width + 20 Then DataGridViewTenChu.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DateNgayMuon_ValueChanged(sender As Object, e As EventArgs) Handles DateNgayMuon.ValueChanged

    End Sub
End Class